﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using LibHuman;

namespace Human
{
    public partial class frm_bcnangbacluongdungthoihan : Form
    {
        AccessData mDB = new AccessData();
        Language lan = new Language();
        ExportToExcel exp = new ExportToExcel();
        string tungay = "", denngay = "", ngay = "",title = "", s_mmyy = "", user = "", kt_thang = "", kt_nam = "", kt_denngay = "",s_bophan="",user_dn="" ;
        DataSet ds;
        DataSet ds_nv = new DataSet();
        DataSet ds_nv_luong = new DataSet();
        DataSet ds_nluong = new DataSet();
        DataSet ds_luong = new DataSet();
        DataSet ds_nhomn = new DataSet();
        string s_manhom = "", s_tennhom = "";
        public frm_bcnangbacluongdungthoihan(string s_makp,string s_userdn)
        {
            InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
            s_bophan = s_makp;
            user_dn = s_userdn;
        }
        
        private void frm_bcnangbacluongdungthoihan_Load(object sender, EventArgs e)
        {
            user = mDB.user;
            if (mDB.Mabv.Trim() == "209.5.09" || mDB.Mabv.Trim() == "209.5.04" || mDB.Mabv.Trim() == "209.5.06")
            {
                lbl_nhom.Visible = true;
                cbo_nhom.Visible = true;
                cbo_nhom.SelectedIndex = 0;
            }
            load_nhomngach();
        }
        private void load_nhomngach()
        {
            ds_nhomn = mDB.get_nhomngach();
            foreach (DataRow dr in ds_nhomn.Tables[0].Rows)
            {
                chkl_nhomngach.Items.Add(dr["tennhom"].ToString());
            }
        }
        private string g_manhom(string str)
        {
            string s = "";
            if (str.Trim().Length > 0)
            {
                string[] s1 = str.Split(',');
                for (int i = 0; i < s1.Length; i++)
                {
                    s = s + "'" + s1[i].ToString().Trim() + "',";
                }
            }
            return s;
        }        
        private DataSet tao_dataset()
        {
            DataSet ds_tmp = new DataSet();
            DataTable dt = new DataTable("Table0");
            DataTable dt_ts = new DataTable("Table1");
            dt_ts.Columns.Add("nv_cuoiky", typeof(string));
            dt_ts.Columns.Add("nv_nbacluong", typeof(string));
            dt_ts.Columns.Add("nv_nbactruoc", typeof(string));
            //
            dt.Columns.Add("stt", typeof(int));
            dt.Columns.Add("manv", typeof(string));
            dt.Columns.Add("hoten", typeof(string));
            dt.Columns.Add("nam", typeof(string));
            dt.Columns.Add("ngaysinhnam", typeof(string));
            dt.Columns.Add("nu", typeof(string));
            dt.Columns.Add("ngaysinhnu", typeof(string));
            
            dt.Columns.Add("td_chuyenmon", typeof(string));
            dt.Columns.Add("mangachcu", typeof(string));
            dt.Columns.Add("mangachmoi", typeof(string));
            dt.Columns.Add("bacluongcu", typeof(decimal));
            dt.Columns.Add("hs_lc_cu", typeof(decimal));
            dt.Columns.Add("hsvk_cu", typeof(decimal));
            dt.Columns.Add("bacluongmoi", typeof(decimal));
            dt.Columns.Add("hs_lc_moi", typeof(decimal));
            dt.Columns.Add("hsvk_moi", typeof(decimal));
            dt.Columns.Add("hs_bluu", typeof(decimal));
            dt.Columns.Add("hs_chenhlech", typeof(decimal));
            dt.Columns.Add("hs_bluu_moi", typeof(decimal));
            dt.Columns.Add("mltt", typeof(decimal));
            dt.Columns.Add("st_cl", typeof(int));
            dt.Columns.Add("ngaygiubac", typeof(string));
            dt.Columns.Add("ngaynangbac", typeof(string));
            dt.Columns.Add("ngay_nang_mltt", typeof(string));
            dt.Columns.Add("ngay", typeof(string));
            dt.Columns.Add("mmyy", typeof(string));
            dt.Columns.Add("nhomngach",typeof(string));
            dt.Columns.Add("tennhom",typeof(string));
            dt.Columns.Add("ghichu", typeof(string));
            ds_tmp.Tables.Add(dt);
            ds_tmp.Tables.Add(dt_ts);
            return ds_tmp;
        }
        //kiem tra du lieu trong bao nhieu thang
        private bool kiemtra()
        {
            tungay = haison1.tungay;
            denngay = haison1.denngay;
            kt_thang = haison1.denngay.Substring(3, 2);
            kt_nam = haison1.denngay.Substring(8, 2);
            kt_denngay = kt_thang + kt_nam;
            

            DateTime dt1 = mDB.StringToDate(tungay);
            DateTime dt2 = mDB.StringToDate(denngay);
            int y1 = dt1.Year, m1 = dt1.Month;
            int y2 = dt2.Year, m2 = dt2.Month;
            int itu, iden;

            string mmyy = "";
            for (int i = y1; i <= y2; i++)
            {
                itu = (i == y1) ? m1 : 1;
                iden = (i == y2) ? m2 : 12;
                for (int j = itu; j <= iden; j++)
                {
                    mmyy = j.ToString().PadLeft(2, '0') + i.ToString().Substring(2, 2);
                    if (mDB.bMmyy(mmyy))
                    {
                        s_mmyy = mmyy;
                        ds_luong.Merge(get_nangluong_thuongxuyen(s_mmyy));                        
                    }
                }
            }
            ds_nv_luong = get_nhanvien();
            return true;
        }
        
        private void btnxem_Click(object sender, EventArgs e)
        {
            ds_nluong = tao_dataset();
            DataRow nrow;
            DataRow nrow1;
            if (kiemtra() == true)
            {
                decimal st_cl = 0;
                int dong = -1;
                ds = get_dsnv_thuongxuyen(s_mmyy);
                if (ds_nv_luong.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds_nv_luong.Tables[0].Rows)
                    {
                        nrow1 = ds_nluong.Tables["Table1"].NewRow();
                        nrow1["nv_cuoiky"] = dr["nv_cuoiky"].ToString();
                        nrow1["nv_nbacluong"] = dr["nv_nbacluong"].ToString();
                        nrow1["nv_nbactruoc"] = dr["nv_nbactruoc"].ToString();
                        ds_nluong.Tables["Table1"].Rows.Add(nrow1);
                    }
                }
                //
                if (ds_luong.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        int flag = 0, kt = 0;
                        st_cl = 0;
                        DataRow[] dr1 = ds_luong.Tables[0].Select("manv= '" + dr["manv"].ToString() + "'", "mltt asc");
                        string str = dr1[0]["manv"].ToString();
                        decimal kt_mltt = 0;
                       
                        if (dr1.Length > 0)
                        {
                            kt_mltt = dr1[0]["mltt"].ToString().Trim() != "" ? decimal.Parse(dr1[0]["mltt"].ToString().Trim()) : 0;
                        }
                        foreach (DataRow r in dr1)
                        {
                            if (kt_mltt < decimal.Parse(r["mltt"].ToString().Trim())) // so sanh hai muc luong toi thieu khong bang nhau
                            {
                                nrow = ds_nluong.Tables["Table0"].NewRow();
                                string ghichu = "";
                                st_cl = 0;
                                st_cl += 1;
                                if (kt_mltt < decimal.Parse(r["mltt"].ToString().Trim()))
                                {
                                    ghichu = "MLTT: " + r["mltt"].ToString().Trim();
                                    kt_mltt = decimal.Parse(r["mltt"].ToString().Trim());
                                }
                                nrow = ds_nluong.Tables["Table0"].NewRow();
                                nrow["bacluongcu"] = r["bacluongcu"].ToString();
                                nrow["hs_lc_cu"] = r["hs_lc_cu"].ToString();
                                nrow["bacluongmoi"] = r["bacluongmoi"].ToString();
                                nrow["hs_lc_moi"] = r["hs_lc_moi"].ToString();
                                nrow["hsvk_cu"] = r["hsvk_cu"].ToString();
                                nrow["hsvk_moi"] = r["hsvk_moi"].ToString();
                                nrow["ngaygiubac"] = r["ngaygiubac"].ToString();
                                nrow["ngaynangbac"] = r["ngaynangbac"].ToString();
                                nrow["ngay_nang_mltt"] = r["ngay_nang_mltt"].ToString();
                                nrow["ngay"] = r["ngay"].ToString();
                                nrow["st_cl"] = st_cl.ToString(); 
                                nrow["mltt"] = r["mltt"].ToString();
                                nrow["hs_chenhlech"] = r["hs_chenhlech"].ToString();
                                nrow["hs_bluu"] = r["hs_bluu"].ToString();
                                nrow["hs_bluu_moi"] = r["hs_bluu_moi"].ToString();
                                nrow["mmyy"] = r["mmyy"].ToString();
                                nrow["nhomngach"] = r["nhomngach"].ToString();
                                nrow["tennhom"] = r["tennhom"].ToString();
                                nrow["ghichu"] = ghichu;
                                ds_nluong.Tables["Table0"].Rows.Add(nrow);
                                dong += 1; // duyet dong tiep theo
                                flag = 1;
                                kt = 1;
                                //st_cl = 0;
                               
                            }
                            else 
                            {
                                flag = 0;
                                nrow = ds_nluong.Tables["Table0"].NewRow();
                                st_cl += 1;
                                nrow["manv"] = r["manv"].ToString();
                                nrow["hoten"] = r["hoten"].ToString();
                                nrow["nam"] = r["nam"].ToString(); 
                                nrow["ngaysinhnam"] = r["ngaysinhnam"].ToString();
                                nrow["nu"] = r["nu"].ToString();
                                nrow["ngaysinhnu"] = r["ngaysinhnu"].ToString();
                                nrow["td_chuyenmon"] = r["td_chuyenmon"].ToString();
                                nrow["mangachcu"] = r["mangachcu"].ToString();
                                nrow["mangachmoi"] = r["mangachmoi"].ToString();
                                nrow["bacluongcu"] = r["bacluongcu"].ToString();
                                nrow["hs_lc_cu"] = r["hs_lc_cu"].ToString();
                                nrow["bacluongmoi"] = r["bacluongmoi"].ToString();
                                nrow["hs_lc_moi"] = r["hs_lc_moi"].ToString();
                                nrow["hsvk_cu"] = r["hsvk_cu"].ToString();
                                nrow["hsvk_moi"] = r["hsvk_moi"].ToString();
                                nrow["ngaygiubac"] = r["ngaygiubac"].ToString(); 
                                nrow["ngaynangbac"] = r["ngaynangbac"].ToString();
                                nrow["ngay_nang_mltt"] = r["ngay_nang_mltt"].ToString();
                                nrow["ngay"] = r["ngay"].ToString();
                                nrow["st_cl"] = st_cl.ToString(); 
                                nrow["mltt"] = r["mltt"].ToString();
                                nrow["hs_chenhlech"] = r["hs_chenhlech"].ToString();
                                nrow["hs_bluu"] = r["hs_bluu"].ToString();
                                nrow["hs_bluu_moi"] = r["hs_bluu_moi"].ToString();
                                nrow["mmyy"] = r["mmyy"].ToString();
                                nrow["nhomngach"] = r["nhomngach"].ToString();
                                nrow["tennhom"] = r["tennhom"].ToString();
                                nrow["ghichu"] = "MLTT: " + kt_mltt.ToString();
                                // truong hop 2 muc luong toi thieu bang nhau
                                if ((kt_mltt == decimal.Parse(r["mltt"].ToString().Trim())) && kt == 1)
                                {
                                    flag = 0;
                                    //st_cl += 1; 
                                    ds_nluong.Tables["Table0"].Rows[dong]["st_cl"] = st_cl.ToString();
                                }
                                else
                                    if (st_cl == 1 && flag == 0)
                                    {
                                        ds_nluong.Tables["Table0"].Rows.Add(nrow);
                                        dong += 1;
                                    }
                                if (st_cl > 1 & flag == 0)
                                {
                                    ds_nluong.Tables["Table0"].Rows[dong]["st_cl"] = st_cl.ToString();
                                }
                            }
                        }
                    }
                }

                ds_nluong.WriteXml("..\\xml\\h_kqnangbacluongdungthoihan.xml", XmlWriteMode.WriteSchema);
                if (ds_nluong.Tables["Table0"].Rows.Count > 0)
                {
                    frmReport f = new frmReport(mDB, ds_nluong,"h_kqnangbacluongdungthoihan.rpt", title, lan.Change_language_MessageText("(từ ngày ") + tungay + lan.Change_language_MessageText(" đến ngày ") + denngay + ")",user_dn);
                    f.ShowDialog();
                    ds_nluong.Clear();
                    ds_luong.Clear();
                    ds.Clear();
                }
                else
                {
                    MessageBox.Show(lan.Change_language_MessageText("Chưa có dữ liệu"), lan.Change_language_MessageText("Thông báo"), MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
        }
        public DataSet get_nhanvien()
        {
            string sql = " select count(distinct l.manv) as nv_cuoiky,";
            sql += " sum(case when to_date(to_char(b.ngaynangbac,'dd/mm/yyyy'),'dd/mm/yyyy')";
            sql += " between to_date('" + tungay + "','dd/mm/yyyy') and to_date('" + denngay + "','dd/mm/yyyy')";
            sql += " and (case when mn.loaingach in ('A0','A1','A2','A3')";
            sql += " then EXTRACT(year from age(to_timestamp(b.ngaynangbac,'yyyy-mm'),to_timestamp(b.ngaygiubac,'yyyy-mm'))) else 0 end =3 ";
            sql += " or case when mn.loaingach in ('B','C') then EXTRACT(year from age(to_timestamp(b.ngaynangbac,'yyyy-mm'),to_timestamp(b.ngaygiubac,'yyyy-mm'))) else 0 end =2)";
            sql += " then 1 else 0 end) as nv_nbacluong,";
            sql += " sum(case when to_timestamp(to_char(b.ngaynangbac,'dd/mm/yyyy'),'dd/mm/yyyy') between to_timestamp('" + tungay + "','dd/mm/yyyy') and to_timestamp('" + denngay + "','dd/mm/yyyy')";
            sql += " and ((case when mn.loaingach in ('A0','A1','A2','A3')";
            sql += " then EXTRACT(year from age(to_timestamp(b.ngaynangbac,'yyyy-mm'),to_timestamp(b.ngaygiubac,'yyyy-mm'))) else 0 end >=2 and";
            sql += " case when mn.loaingach in ('A0','A1','A2','A3') then EXTRACT(year from age(to_timestamp(b.ngaynangbac,'yyyy-mm'),to_timestamp(b.ngaygiubac,'yyyy-mm'))) else 0 end <3)";
            sql += " or (case when mn.loaingach in ('B','C')";
            sql += " then EXTRACT(year from age(to_timestamp(b.ngaynangbac,'yyyy-mm'),to_timestamp(b.ngaygiubac,'yyyy-mm'))) else 0 end >=1";
            sql += " and case when mn.loaingach in ('B','C') ";
            sql += " then EXTRACT(year from age(to_timestamp(b.ngaynangbac,'yyyy-mm'),to_timestamp(b.ngaygiubac,'yyyy-mm'))) else 0 end  < 2 ))";
            sql += " then 1 else 0 end) as nv_nbactruoc";
            sql += " from " + user + ".hopdong h ";
            sql += " inner join " + user + ".llnv l on h.manv=l.manv ";
            sql += " left join " + user + ".thaydoi_bluong b on b.manv=h.manv";
            sql += " inner join " + user + ".bophan bp on l.madv =bp.madv";
            sql += " left join " + user + ".mangach mn on h.mangach=mn.ma ";
            sql += " inner join " + user + ".max_lan ml on h.manv=ml.manv ";
            sql += " where to_date(to_char(h.ngaydenct,'dd/mm/yyyy'),'dd/mm/yyyy') <= to_date('" + denngay + "','dd/mm/yyyy')";
            sql += " and (to_date(to_char(h.ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy')>= to_date('" + denngay + "','dd/mm/yyyy') or h.ngaynghi is null) and h.lan=ml.lan";
            if (s_manhom.Trim() != "")
            {
                string s1 = g_manhom(s_manhom.Substring(0, s_manhom.Trim().Length - 1));
                sql += " and mn.nhomngach in (" + s1.Substring(0, s1.Length - 1) + ")";
            }
            if (cbo_nhom.SelectedIndex == 0)
                sql += " and bp.mabsoft in ('L','C','P','K')";
            if (cbo_nhom.SelectedIndex == 1)
                sql += " and bp.mabsoft in ('D')";
            if (cbo_nhom.SelectedIndex == 2)
                sql += " and bp.mabsoft in ('X')";
            return ds_nv_luong = mDB.get_data(sql);
        }
        public DataSet get_dsnv_thuongxuyen(string s_mmyy)
        {
            string sql1 = " select 0 as stt,ll.manv,ll.ho||' '||ll.ten as hoten,0 as st_cl, case when ll.phai='Nam' then to_char(ll.ngaysinh,'yyyy') end as nam,m.nhomngach,nn.tennhom,";
            sql1 += " case when ll.phai='Nam' then to_char(ll.ngaysinh,'dd/mm/yyyy') end as ngaysinhnam,trim(ct.tenctdt)||' - '||trim(bc.tenbang) as td_chuyenmon, case when ll.phai<>'Nam' then to_char(ll.ngaysinh,'yyyy') end as nu,";
            sql1 += " case when ll.phai<>'Nam' then to_char(ll.ngaysinh,'dd/mm/yyyy') end as ngaysinhnu,m.loaingach, bl.mangachcu,trim(substring(trim(bl.bacluongcu),1,2)) as bacluongcu,";
            sql1 += " to_number(trim(substring(trim(bl.bacluongcu),6))) as hs_lc_cu,0.0 as hs_bluu,bl.mangachmoi,trim(substring(trim(bl.bacluongmoi),1,2)) as bacluongmoi,";
            sql1 += " to_number(trim(substring(trim(bl.bacluongmoi),6))) as hs_lc_moi,0.0 as hs_bluu_moi,hs.mltt as mltt,'' as ghichu,0.0 as hsvk_cu,0.0 as hsvk_moi,";
            sql1 += " 0.0 as hs_chenhlech,bl.ngay,bl.mmyy,to_char(bl.ngaygiubac,'dd/mm/yyyy') as ngaygiubac,to_char(bl.ngaynangbac,'dd/mm/yyyy')as ngaynangbac,hs.mmyy as ngay_nang_mltt ";
            sql1 += " from " + user + ".llnv ll ";
            sql1 += " inner join " + user + ".hopdong hd on hd.manv=ll.manv  ";
            sql1 += " inner join " + user + ".bophan bp on ll.madv =bp.madv";
            sql1 += " inner join " + user + ".thaydoi_bluong bl on bl.manv=ll.manv ";
            sql1 += " inner join " + user + ".mangach m on m.ma1=bl.mangachcu  ";
            sql1 += " inner join " + user + ".max_lan ml on hd.manv=ml.manv  ";
            sql1 += " left join " + user + ".ctdaotao ct on ct.mactdt=ll.nnghiep  ";
            sql1 += " left join " + user + ".bangcap bc on ll.td_nnghiep=bc.mabangcap ";
            sql1 += " left join " + user + ".heso_nv_" + s_mmyy + " g on ll.manv=g.manv ";
            sql1 += " inner join " + user + ".heso hs on g.mmyy=hs.mmyy ";
            sql1 += " inner join " + user + ".nhomngach nn on nn.manhom=m.nhomngach";
            sql1 += " where to_timestamp(to_char(bl.ngaynangbac,'dd/mm/yyyy'),'dd/mm/yyyy')";
            sql1 += " between to_timestamp('" + tungay + "','dd/mm/yyyy')  and to_timestamp('" + denngay + "','dd/mm/yyyy')  and trim(substring(trim(bl.bacluongmoi),1,2))<>'1' ";
            sql1 += " and (hd.ngaynghi is null or to_timestamp(to_char(hd.ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy')>to_timestamp('" + denngay + "','dd/mm/yyyy')) and hd.lan=ml.lan  ";
            sql1 += " and (m.loaingach in ('A0','A1','A2','A3') and  extract(year from age(to_timestamp(to_char(bl.ngaynangbac,'dd/mm/yyyy'),'dd/mm/yyyy'),";
            sql1 += " to_timestamp(to_char(bl.ngaygiubac,'dd/mm/yyyy'),'dd/mm/yyyy')))=3";
            sql1 += " or m.loaingach in ('B','C') and extract(year from age(to_timestamp(to_char(bl.ngaynangbac,'dd/mm/yyyy'),'dd/mm/yyyy'),to_timestamp(to_char(bl.ngaygiubac,'dd/mm/yyyy'),'dd/mm/yyyy'))) =2)";
            sql1 += " and to_date(to_char(bl.ngay,'dd/mm/yyyy'),'dd/mm/yyyy') between to_date('" + tungay + "','dd/mm/yyyy') and to_date('" + denngay + "','dd/mm/yyyy') ";
            if (s_manhom.Trim() != "")
            {
                string s1 = g_manhom(s_manhom.Substring(0, s_manhom.Trim().Length - 1));
                sql1 += " and m.nhomngach in (" + s1.Substring(0, s1.Length - 1) + ")";
            }
            if (cbo_nhom.SelectedIndex == 0)
                sql1 += " and bp.mabsoft in ('L','C','P','K')";
            if (cbo_nhom.SelectedIndex == 1)
                sql1 += " and bp.mabsoft in ('D')";
            if (cbo_nhom.SelectedIndex == 2)
                sql1 += " and bp.mabsoft in ('X')";
            return ds = mDB.get_data(sql1);
        }
        public DataSet get_nangluong_thuongxuyen(string s_mmyy)
        {
            string sql1 = " select 0 as stt,ll.manv,ll.ho||' '||ll.ten as hoten,0 as st_cl, case when ll.phai='Nam' then to_char(ll.ngaysinh,'yyyy') end as nam,m.nhomngach,nn.tennhom,";
            sql1 += " case when ll.phai='Nam' then to_char(ll.ngaysinh,'dd/mm/yyyy') end as ngaysinhnam,trim(ct.tenctdt)||' - '||trim(bc.tenbang) as td_chuyenmon, case when ll.phai<>'Nam' then to_char(ll.ngaysinh,'yyyy') end as nu,";
            sql1 += " case when ll.phai<>'Nam' then to_char(ll.ngaysinh,'dd/mm/yyyy') end as ngaysinhnu,m.loaingach, bl.mangachcu,trim(substring(trim(bl.bacluongcu),1,2)) as bacluongcu,";
            sql1 += " to_number(trim(substring(trim(bl.bacluongcu),6))) as hs_lc_cu,0.0 as hs_bluu,bl.mangachmoi,trim(substring(trim(bl.bacluongmoi),1,2)) as bacluongmoi,";
            sql1 += " to_number(trim(substring(trim(bl.bacluongmoi),6))) as hs_lc_moi,0.0 as hs_bluu_moi,hs.mltt as mltt,'' as ghichu,0.0 as hsvk_cu,0.0 as hsvk_moi,";
            sql1 += " 0.0 as hs_chenhlech,bl.ngay,bl.mmyy,to_char(bl.ngaygiubac,'dd/mm/yyyy') as ngaygiubac,to_char(bl.ngaynangbac,'dd/mm/yyyy')as ngaynangbac,hs.mmyy as ngay_nang_mltt ";
            sql1 += " from " + user + ".llnv ll ";
            sql1 += " inner join " + user + ".hopdong hd on hd.manv=ll.manv  ";
            sql1 += " inner join " + user + ".bophan bp on ll.madv =bp.madv";
            sql1 += " inner join " + user + ".thaydoi_bluong bl on bl.manv=ll.manv ";
            sql1 += " inner join " + user + ".mangach m on m.ma1=bl.mangachcu  "; 
            sql1 += " inner join " + user + ".max_lan ml on hd.manv=ml.manv  ";
            sql1 += " left join " + user + ".ctdaotao ct on ct.mactdt=ll.nnghiep  ";
            sql1 += " left join " + user + ".bangcap bc on ll.td_nnghiep=bc.mabangcap ";
            sql1 += " left join " + user + ".heso_nv_" + s_mmyy + " g on ll.manv=g.manv ";
            sql1 += " inner join " + user + ".heso hs on g.mmyy=hs.mmyy ";
            sql1 += " inner join " + user + ".nhomngach nn on nn.manhom=m.nhomngach";
            sql1 += " where to_timestamp(to_char(bl.ngaynangbac,'dd/mm/yyyy'),'dd/mm/yyyy')";
            sql1 += " between to_timestamp('" + tungay + "','dd/mm/yyyy')  and to_timestamp('" + denngay + "','dd/mm/yyyy')  and trim(substring(trim(bl.bacluongmoi),1,2))<>'1' ";
            sql1 += " and (hd.ngaynghi is null or to_timestamp(to_char(hd.ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy')>to_timestamp('" + denngay + "','dd/mm/yyyy')) and hd.lan=ml.lan  ";
            sql1 += " and (m.loaingach in ('A0','A1','A2','A3') and  extract(year from age(to_timestamp(to_char(bl.ngaynangbac,'dd/mm/yyyy'),'dd/mm/yyyy'),";
            sql1 += " to_timestamp(to_char(bl.ngaygiubac,'dd/mm/yyyy'),'dd/mm/yyyy')))=3";
            sql1 += " or m.loaingach in ('B','C') and extract(year from age(to_timestamp(to_char(bl.ngaynangbac,'dd/mm/yyyy'),'dd/mm/yyyy'),to_timestamp(to_char(bl.ngaygiubac,'dd/mm/yyyy'),'dd/mm/yyyy'))) =2)";
            sql1 += " and to_date(to_char(bl.ngay,'dd/mm/yyyy'),'dd/mm/yyyy') between to_date('" + tungay + "','dd/mm/yyyy') and to_date('" + denngay + "','dd/mm/yyyy') ";
            sql1 += " and case when to_date(to_char(bl.ngay,'mmyy'),'mmyy') > to_date('" + s_mmyy + "','mmyy') then true else false end";
            sql1 += " and to_date(to_char(bl.ngaynangbac,'mmyy'),'mmyy') <= to_date('" + s_mmyy + "','mmyy') ";
            if (s_manhom.Trim() != "")
            {
                string s1 = g_manhom(s_manhom.Substring(0, s_manhom.Trim().Length - 1));
                sql1 += " and m.nhomngach in (" + s1.Substring(0, s1.Length - 1) + ")";
            }
            if (cbo_nhom.SelectedIndex == 0)
                sql1 += " and bp.mabsoft in ('L','C','P','K')";
            if (cbo_nhom.SelectedIndex == 1)
                sql1 += " and bp.mabsoft in ('D')";
            if (cbo_nhom.SelectedIndex == 2)
                sql1 += " and bp.mabsoft in ('X')";
            ds_nv = mDB.get_data(sql1);
            int i = 1;
            decimal moi = 0, cu = 0, clech = 0;
            if (ds_nv != null && ds_nv.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds_nv.Tables[0].Rows)
                {
                    dr["stt"] = i.ToString().PadLeft(2, '0');
                    i++;
                    cu = dr["hs_lc_cu"].ToString().Trim() != "" ? decimal.Parse(dr["hs_lc_cu"].ToString()) : 0;
                    moi = dr["hs_lc_moi"].ToString().Trim() != "" ? decimal.Parse(dr["hs_lc_moi"].ToString()) : 0;
                    clech = moi - cu;
                    dr["hs_chenhlech"] = clech;
                }
            }
            return ds_nv;
        }
        /*public DataSet exp_exel(DataSet dse)
        {

            DataSet dse1 = new DataSet();
            dse1.Tables.Add();
            dse1.Tables[0].Columns.Add("stt", typeof(string));
            dse1.Tables[0].Columns.Add("hoten", typeof(string));
            dse1.Tables[0].Columns.Add("ngaysinhnam", typeof(string));
            dse1.Tables[0].Columns.Add("ngaysinhnu", typeof(string));
            dse1.Tables[0].Columns.Add("mangachcu", typeof(string));
            dse1.Tables[0].Columns.Add("bacluongcu", typeof(string));
            dse1.Tables[0].Columns.Add("hs_lc_cu", typeof(string));
            dse1.Tables[0].Columns.Add("hsvk_cu", typeof(string));
            dse1.Tables[0].Columns.Add("ngaygiubac", typeof(string));
            dse1.Tables[0].Columns.Add("hs_bluu", typeof(string));
            dse1.Tables[0].Columns.Add("mangachmoi", typeof(string));
            dse1.Tables[0].Columns.Add("bacluongmoi", typeof(string));
            dse1.Tables[0].Columns.Add("hs_lc_moi", typeof(string));
            dse1.Tables[0].Columns.Add("hsvk_moi", typeof(string));
            dse1.Tables[0].Columns.Add("hs_chenhlech", typeof(string));
            dse1.Tables[0].Columns.Add("hs_bluu_moi", typeof(string));
            dse1.Tables[0].Columns.Add("st_chenhlech", typeof(string));
            dse1.Tables[0].Columns.Add("hsvk_moi", typeof(string));
            dse1.Tables[0].Columns.Add("ghichu", typeof(string));

            int stt = 0;
            DataRow nrow;
            foreach (DataRow r in dse.Tables[1].Rows)
            {
                stt++;
                nrow = dse1.Tables[0].NewRow();
                nrow["stt"] = stt.ToString();
                nrow["hoten"] = r["hoten"].ToString();
                nrow["ngaysinhnam"] = r["ngaysinhnam"].ToString();
                nrow["ngaysinhnu"] = r["ngaysinhnu"].ToString();
                nrow["mangachcu"] = r["mangachcu"].ToString();
                nrow["bacluongcu"] = r["bacluongcu"].ToString();
                nrow["hs_lc_cu"] = r["hs_lc_cu"].ToString();
                nrow["hsvk_cu"] = r["hsvk_cu"].ToString();
                nrow["ngaygiubac"] = r["ngaygiubac"].ToString();
                nrow["hs_bluu"] = r["hs_bluu"].ToString();
                nrow["mangachmoi"] = r["mangachmoi"].ToString();
                nrow["bacluongmoi"] = r["bacluongmoi"].ToString();
                nrow["hs_lc_moi"] = r["hs_lc_moi"].ToString();
                nrow["hsvk_moi"] = r["hsvk_moi"].ToString();
                nrow["ngaynangbac"] = r["ngaynangbac"].ToString();
                nrow["hs_chenhlech"] = r["hs_chenhlech"].ToString();
                nrow["hs_bluu_moi"] = r["hs_bluu_moi"].ToString();
                nrow["st_chenhlech"] = r["st_chenhlech"].ToString();
                nrow["ghichu"] = r["ghichu"].ToString();
                dse1.Tables[0].Rows.Add(nrow);
            }
            return dse1;
        }

        private void btn_exel_Click(object sender, EventArgs e)
        {
            string nam = haison1.denngay.Substring(6,4);
           
            getdata();
            DataSet ds1 = exp_exel(ds);
            exp.exp_bc_nangbacluong_quy(ds1, "bc_nangbacluong_quy", msg, nam,s_mmyy);
        }*/

        private void btnketthuc_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void haison1_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
        }

        private void btnxem_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
        }

        private void btn_exel_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
        }

        private void chkl_nhomngach_Validated(object sender, EventArgs e)
        {
            try
            {
                chkl_nhomngach.SetItemCheckState(chkl_nhomngach.SelectedIndex, CheckState.Checked);
                s_manhom = ""; s_tennhom = "";
                for (int i = 0; i < chkl_nhomngach.Items.Count; i++)
                {
                    if (chkl_nhomngach.GetItemChecked(i))
                    {
                        s_manhom += mDB.getrowbyid(ds_nhomn.Tables[0], "tennhom='" + chkl_nhomngach.Items[i].ToString() + "'")["manhom"].ToString() + ",";
                        s_tennhom += chkl_nhomngach.Items[i].ToString() + ",";
                    }
                }
                s_tennhom = s_tennhom.Trim().Substring(0, s_tennhom.Length - 1);
                txt_tieude.Text = s_tennhom;
            }
            catch { }
        }

        private void btn_exel_Click(object sender, EventArgs e)
        {

        }

   
    }
}